﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ReporteInventario')
	BEGIN
		DROP  Procedure  ReporteInventario
	END

GO

CREATE Procedure ReporteInventario

	(
		@InsumoID int = NULL
	)

AS

SELECT     
	ISNULL(dbo.Almacenes.nombre, 'Sin Almacen') AS Almacen, 
	dbo.Almacenamientos.stock AS StockAlmacen, 
	ISNULL(CONVERT(varchar, dbo.Lotes.numero), 'Sin Lote') AS Lote, 
	dbo.Lotes.stock AS StockLote, 
	dbo.Lotes.fechaVencimiento AS FechaVencimiento, 
	dbo.Insumos.nombre AS Insumo, dbo.Insumos.stock AS StockInsumo
FROM dbo.Insumos 
LEFT OUTER JOIN dbo.Lotes 
LEFT OUTER JOIN dbo.Almacenamientos 
INNER JOIN dbo.Almacenes ON dbo.Almacenamientos.almacenID = dbo.Almacenes.almacenID ON dbo.Lotes.loteID = dbo.Almacenamientos.loteID ON dbo.Insumos.insumoID = dbo.Lotes.insumoID
WHERE dbo.Almacenamientos.stock > 0 AND
	CASE 
		WHEN @InsumoID IS NULL THEN 1
		WHEN @InsumoID = dbo.Insumos.insumoID THEN 1
		ELSE 0
	END = 1 	
ORDER BY Almacen

GO

GRANT EXEC ON ReporteInventario TO PUBLIC

GO


